<!--
    Copyright 2010 Justin Lipton, Jonathan Rosenberg
    
    This file is part of MediaList.

    MediaList is free software: you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation, either version 3 of the License, or
    (at your option) any later version.

    MediaList is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU General Public License for more details.

    You should have received a copy of the GNU General Public License
    along with MediaList.  If not, see <http://www.gnu.org/licenses/>.
-->

<div class=header>MediaList!</div>
<div class=body>
<p style="text-align:center;">Welcome to MediaList!  Please login!</p>
<span style="text-align:left;"><a href=register.php>Register</a></span>
<span style="text-align:right;margin-left:570px">
  Welcome guest!(<a href=login.php>Login</a>)
</span>
</div>
<h1>
<a href=second.html>Second page</a>
<br />
<a href=addNewShow.php>Add a new show</a>
<br />
</h1>
<?php
    $connect = mysql_connect("localhost", "root") 
    or die("Could not connect to SQL server");
    
    if (mysql_query("CREATE DATABASE DB",$connect))
    {
        echo "DB created";
    }
    
    mysql_select_db("DB", $connect);
    
    //mysql_query("DROP TABLE Users", $connect); //uncomment to drop the Users table
                
    $check = mysql_query("SELECT UserId FROM Users");
    
    if(!$check)
    {
        $sql = "CREATE TABLE Users
        (
            Username varchar(50) NOT NULL PRIMARY KEY,
            Password varchar(255) NOT NULL,
            Email varchar(50) NOT NULL,
            Fav1 mediumint UNSIGNED,
            Fav2 mediumint UNSIGNED,
            Fav3 mediumint UNSIGNED,
            Fav4 mediumint UNSIGNED,
            Fav5 mediumint UNSIGNED,
            Gender char(1),
            Birthday DATE,
            Location varchar(50),
            JoinDay DATE,
            NumForumPosts mediumint UNSIGNED DEFAULT 0,
            TotalTimeMinutes mediumint UNSIGNED DEFAULT 0,
            NumViewing mediumint UNSIGNED DEFAULT 0,
            NumCompleted mediumint UNSIGNED DEFAULT 0,
            NumDropped mediumint UNSIGNED DEFAULT 0,
            NumPlanning mediumint UNSIGNED DEFAULT 0,
            Privileges char(1)
        )";
        //privileges is either A=admin, M=moderator, or R=regular
        // Execute query
        mysql_query($sql,$connect);
        
    }     
    
    $check = mysql_query("SELECT ShowId FROM Shows");
    
    if(!$check)
    {
        $sql = "CREATE TABLE Shows
        ( 
            ShowId mediumint UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            Showname varchar(50) NOT NULL,
            ForumLink varchar(255),
            NumEpisodes smallint UNSIGNED,
            EpisodeLength smallint UNSIGNED,
            Summary varchar(1500),
            WhenMade DATE,
            TotalScores mediumint UNSIGNED DEFAULT 0,
            TotalSeen mediumint UNSIGNED DEFAULT 0,
            TotalFav mediumint UNSIGNED DEFAULT 0                  
        )";  //tags/genres?
        //Total score is the total of all the ratings, combined with totalSeen creates average
        //when add new show, create a table named the showname, 
        //that stores all userids who have seen it
        mysql_query($sql,$connect);
    }
    
    $check = mysql_query("SELECT ShowId FROM UserShowJoin");
    
    if(!$check)
    {
        $sql = "CREATE TABLE UserShowJoin
        ( 
            Username varchar(50) NOT NULL,
            Showname varchar(50) NOT NULL,
            Rating tinyint,
            FOREIGN KEY(Username) references Users(Username) ON DELETE CASCADE,
            FOREIGN KEY(Showname) references Shows(Showname) ON DELETE CASCADE,
            PRIMARY KEY(Username, Showname)
        )";  
        mysql_query($sql,$connect);
    }
       
    $check = mysql_query("SELECT user_from FROM correlated_users");
    
    if(!$check)
    {
        $sql="CREATE VIEW correlated_users AS
        SELECT ja.Username AS user_from, jb.Username AS user_to,
               SUM(CASE WHEN ABS(ja.rating-jb.rating) < 2 THEN 1 
            WHEN ABS(ja.rating-jb.rating)>4 THEN -1 ELSE 0 END)AS 'compatibility' 
        FROM UserShowJoin ja, UserShowJoin jb
        WHERE ja.Showname = jb.Showname AND ja.Username<>jb.Username GROUP BY ja.Username, jb.Username";
        mysql_query($sql,$connect);
        echo mysql_error();
    }
                
?>